using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;
using System.Windows.Forms;
using Microsoft.Win32;

using FreeFlow.Administration;
using FreeFlow.WinFormsControls.HelperClasses;

namespace FreeFlowAdministrator
{
	/// <summary>
	/// Summary description for SqlWindowControl.
	/// </summary>
	public class SqlWindowControl : AdminUserControl
	{
    private System.Windows.Forms.TextBox sqlTextBox;
    private System.Windows.Forms.Panel panel1;
    private ExtendedDataGrid resultsGrid;
    private System.Windows.Forms.Splitter splitter1;
    private IContainer components;
    private System.Windows.Forms.Panel panel3;
    private System.Windows.Forms.Label rowCountLabel;
    private FreeFlowAdministrator.UIActionsControl uiActionsControl;

    private const string regKey = "SQL";
		public SqlWindowControl()
		{
			// This call is required by the Windows.Forms Form Designer.
			InitializeComponent();

      RegistryKey key = Registry.CurrentUser.OpenSubKey(Constants.RegistryKey);
      if (key != null)
        if (key.GetValue(regKey) != null)
          sqlTextBox.Text = key.GetValue(regKey).ToString();

      UIActionList actionList = new UIActionList();
      actionList.Add("Execute", "Execute the specified SQL", 
        new ActionExecuteDelegate(ExecuteSql));
      actionList.Add("Copy to clipboard", "Copy the results to the clipboard", 
        new ActionExecuteDelegate(CopyToClipboard));
      actionList.Add("Open", "Open a SQL query file to execute", 
        new ActionExecuteDelegate(OpenSql));
      uiActionsControl.Actions = actionList;
		}

		/// <summary> 
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

    public override bool CanDispose
    {
      get
      {
        return false;
      }
    }

		#region Component Designer generated code
		/// <summary> 
		/// Required method for Designer support - do not modify 
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
      this.components = new System.ComponentModel.Container();
      this.sqlTextBox = new System.Windows.Forms.TextBox();
      this.panel1 = new System.Windows.Forms.Panel();
      this.resultsGrid = new FreeFlowAdministrator.ExtendedDataGrid();
      this.splitter1 = new System.Windows.Forms.Splitter();
      this.panel3 = new System.Windows.Forms.Panel();
      this.rowCountLabel = new System.Windows.Forms.Label();
      this.uiActionsControl = new FreeFlowAdministrator.UIActionsControl();
      this.panel1.SuspendLayout();
      ((System.ComponentModel.ISupportInitialize)(this.resultsGrid)).BeginInit();
      this.panel3.SuspendLayout();
      this.SuspendLayout();
      // 
      // sqlTextBox
      // 
      this.sqlTextBox.Dock = System.Windows.Forms.DockStyle.Fill;
      this.sqlTextBox.Font = new System.Drawing.Font("Courier New", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
      this.sqlTextBox.HideSelection = false;
      this.sqlTextBox.Location = new System.Drawing.Point(0, 0);
      this.sqlTextBox.Multiline = true;
      this.sqlTextBox.Name = "sqlTextBox";
      this.sqlTextBox.ScrollBars = System.Windows.Forms.ScrollBars.Vertical;
      this.sqlTextBox.Size = new System.Drawing.Size(562, 72);
      this.sqlTextBox.TabIndex = 0;
      // 
      // panel1
      // 
      this.panel1.Controls.Add(this.sqlTextBox);
      this.panel1.Dock = System.Windows.Forms.DockStyle.Top;
      this.panel1.Location = new System.Drawing.Point(0, 0);
      this.panel1.Name = "panel1";
      this.panel1.Size = new System.Drawing.Size(562, 72);
      this.panel1.TabIndex = 1;
      // 
      // resultsGrid
      // 
      this.resultsGrid.AllowNavigation = false;
      this.resultsGrid.CaptionVisible = false;
      this.resultsGrid.DataMember = "";
      this.resultsGrid.Dock = System.Windows.Forms.DockStyle.Fill;
      this.resultsGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
      this.resultsGrid.Location = new System.Drawing.Point(0, 72);
      this.resultsGrid.Name = "resultsGrid";
      this.resultsGrid.ReadOnly = true;
      this.resultsGrid.Size = new System.Drawing.Size(562, 272);
      this.resultsGrid.TabIndex = 2;
      // 
      // splitter1
      // 
      this.splitter1.Dock = System.Windows.Forms.DockStyle.Top;
      this.splitter1.Location = new System.Drawing.Point(0, 72);
      this.splitter1.Name = "splitter1";
      this.splitter1.Size = new System.Drawing.Size(562, 3);
      this.splitter1.TabIndex = 3;
      this.splitter1.TabStop = false;
      // 
      // panel3
      // 
      this.panel3.Controls.Add(this.rowCountLabel);
      this.panel3.Dock = System.Windows.Forms.DockStyle.Bottom;
      this.panel3.Location = new System.Drawing.Point(0, 344);
      this.panel3.Name = "panel3";
      this.panel3.Size = new System.Drawing.Size(712, 24);
      this.panel3.TabIndex = 4;
      // 
      // rowCountLabel
      // 
      this.rowCountLabel.Dock = System.Windows.Forms.DockStyle.Fill;
      this.rowCountLabel.Location = new System.Drawing.Point(0, 0);
      this.rowCountLabel.Name = "rowCountLabel";
      this.rowCountLabel.Size = new System.Drawing.Size(712, 24);
      this.rowCountLabel.TabIndex = 0;
      this.rowCountLabel.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
      // 
      // uiActionsControl
      // 
      this.uiActionsControl.Actions = null;
      this.uiActionsControl.Dock = System.Windows.Forms.DockStyle.Right;
      this.uiActionsControl.Location = new System.Drawing.Point(562, 0);
      this.uiActionsControl.Name = "uiActionsControl";
      this.uiActionsControl.Size = new System.Drawing.Size(150, 344);
      this.uiActionsControl.TabIndex = 5;
      // 
      // SqlWindowControl
      // 
      this.Controls.Add(this.splitter1);
      this.Controls.Add(this.resultsGrid);
      this.Controls.Add(this.panel1);
      this.Controls.Add(this.uiActionsControl);
      this.Controls.Add(this.panel3);
      this.Name = "SqlWindowControl";
      this.Size = new System.Drawing.Size(712, 368);
      this.panel1.ResumeLayout(false);
      this.panel1.PerformLayout();
      ((System.ComponentModel.ISupportInitialize)(this.resultsGrid)).EndInit();
      this.panel3.ResumeLayout(false);
      this.ResumeLayout(false);

    }
		#endregion

    private Server server;

    public void ExecuteSql(string sql)
    {
      sqlTextBox.Text = sql;
      ExecuteSql();
    }

    private string ExecuteSql()
    {
      string result = "";
      using (new CursorHandler())
      {
        string sqlToExecute = sqlTextBox.Text;
        if (!string.IsNullOrEmpty(sqlTextBox.SelectedText))
          sqlToExecute = sqlTextBox.SelectedText;

        if (string.IsNullOrEmpty(sqlToExecute))
          return "No SQL to execute";

        DataTable results = null;
        try
        {
          results = server.ExecuteDataTable(sqlToExecute);
        }
        catch(OleDbException ex)
        {
          result = ex.Message;
        }
        catch(OdbcException ex)
        {
          result = ex.Message;
        }

        if (results == null)
        {
          rowCountLabel.Text = string.Empty;
          if (string.IsNullOrEmpty(result))
            result = "Query executed";
        }
        else
        {
          rowCountLabel.Text = results.Rows.Count + " rows";
          resultsGrid.DataSource = results;
          resultsGrid.ClearColumns();
          for (int i = 0; i < results.Columns.Count; i++)
          {
            resultsGrid.AddColumn(results.Columns[i].ColumnName);
          }
          resultsGrid.AutosizeColumns();
          result = "Query returned " + results.Rows.Count + " rows";
        }
        RegistryKey key = Registry.CurrentUser.CreateSubKey(Constants.RegistryKey);
        key.SetValue(regKey, sqlTextBox.Text);
        return result;
      }
    }
  
    private string CopyToClipboard()
    {
      resultsGrid.CopyToClipboard();
      return null;
    }

    private string OpenSql()
    {
      using (OpenFileDialog openDialog = new OpenFileDialog())
      {
        openDialog.Filter = "SQL files (*.sql)|*.sql|All files(*.*)|*.*";
        if (openDialog.ShowDialog() == DialogResult.OK)
        {
          // load the file
          using (StreamReader reader = new StreamReader(openDialog.FileName))
          {
            sqlTextBox.Text = reader.ReadToEnd();
          }
        }
      }
      return null;
    }

    public Server Server
    {
      get
      {
        return server;
      }
      set
      {
        server = value;
      }
    }
	}
}
